"""
Case Type   : 客户场景-邮储
Case Name   : 简单插入语句及index scan 查询语句支持bypass优化
Create At   : 2021/11/25
Owner       : wan005
Description :
    1.创建表
    2.解析插入语句，仅1个vaules
    3.解析插入语句，由generate_series生成序列
    4.解析插入语句，有多个values
    5.插入数据
    6.创建索引
    7.解析简单查询语句
    8. 查看查询结果
Expect      :
    1. 创建表成功
    2. 插入语句被优化，解析结果包含：【Bypass】
    3. 插入语句不会被优化，接续结果包含【No Bypass】
    4. 插入语句不会被优化，解析结果包含【No Bypass】
    5. 插入数据成功
    6. 创建索引成功
    7. 查询语句被优化，解析结果包含【Bypass】
    8. 查询结果正确
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class Finance(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.constant = Constant()
        self.commonsh = CommonSH("PrimaryDbUser")
        self.common = Common()
        self.user_node = Node("PrimaryDbUser")
        self.root_node = Node("PrimaryRoot")
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        status = self.commonsh.restart_db_cluster()
        self.log.info(status)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

        result = self.commonsh.execut_db_sql("show enable_opfusion")
        self.enable_opfusion = result.splitlines()[-2].strip()
        self.log.info(self.enable_opfusion)
        result = self.commonsh.execut_db_sql("show enable_bitmapscan")
        self.enable_bitmapscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_bitmapscan)
        result = self.commonsh.execut_db_sql("show enable_seqscan")
        self.enable_seqscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_seqscan)
        result = self.commonsh.execut_db_sql("show opfusion_debug_mode")
        self.opfusion_debug_mode = result.splitlines()[-2].strip()
        self.log.info(self.opfusion_debug_mode)

        self.t_name1 = "t_finance_006_01"
        self.t_name2 = "t_finance_006_02"

    def set_gs_guc(self, param, value, method="set"):
        """
        定义一个函数，设置guc参数为需要的值
        param="param":需设置的guc参数
        param="value":guc参数需设置的值
        param="method":设置方式 默认为set
        """
        result = self.commonsh.execut_db_sql(f"show {param}")
        self.log.info(result)
        if f"{value}" != result.splitlines()[-2].strip():
            res = self.commonsh.execute_gsguc(method,
                                              self.constant.GSGUC_SUCCESS_MSG,
                                              f"{param}={value}")
            self.assertTrue(res)
        self.log.info(f"{param} is {value}")

    def test_finance(self):
        text = f"修改参数; expect:成功"
        self.log.info(text)
        self.set_gs_guc("enable_opfusion", "on", "reload")
        self.set_gs_guc("enable_bitmapscan", "off", "reload")
        self.set_gs_guc("enable_seqscan", "off", "reload")
        self.set_gs_guc("opfusion_debug_mode", "'log'", "reload")

        text = f"--step1:创建表; expect:成功"
        self.log.info(text)
        sql1 = f'create table {self.t_name1}(id_int int, ' \
            f'id_tinyint TINYINT, binary_int BINARY_INTEGER, ' \
            f'money_val money, bool_val BOOLEAN, ' \
            f'blob_val BLOB, "RAW_val" RAW, ' \
            f'bytea BYTEA, cidr_addr cidr, ' \
            f'bit_val BIT(3), json_val json, oid_val oid);' \
            f'create table {self.t_name2}(body1 CHARACTER(4), ' \
            f'body2 VARCHAR(7), body3 TEXT,body4 CLOB, ' \
            f'date_val DATE, dai time with time zone,vbg smalldatetime, ' \
            f'point_val point, mac_addr macaddr, ' \
            f'bit_var_val BIT VARYING(5), tsvector_val tsvector, ' \
            f'uuid_val uuid);'
        result = self.commonsh.execut_db_sql(sql1)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败:" + text)

        text = "--step2:解析插入语句，仅1个vaules; expect:解析结果包含：【Bypass】"
        self.log.info(text)
        sql2 = f'''explain insert into {self.t_name1} \
                values(10, 25, -3, +123, null, empty_blob(), \
                'DEADBEEF', E'\\xDEADBEEF', '192.168/25', B'101', \
                '{{{{\\"f1\\":1,\\"f2\\":true,\\"f3\\":\\"Hi\\"}}}}', 564182);
                explain insert into {self.t_name1} \
                values(10, 25, -3, +123, null, empty_blob(), \
                'DEADBEEF', E'\\xDEADBEEF', '192.168/25', \
                B'101', '{{{{\\"f1\\":1,\\"f2\\":true,\\"f3\\":\\"Hi\\"}}}}', 
                564182)
                '''
        sql_list = sql2.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn("Bypass", result, "执行失败:" + text)

        sql3 = f"explain insert into {self.t_name2} " \
            f"values('qw', '+;^kkk', '$￥你好', '哇哦@\~ 777', '2020-12-2', " \
            f"'21:21:21 pst', '2003-04-12 04:05:06', point(1,2), " \
            f"'08:00:2b:01:02:03', B'10', 'The Fat Rats'::tsvector, " \
            f"'{{{{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}}}}');"
        self.log.info(sql3)
        result = self.commonsh.execut_db_sql(sql3)
        self.log.info(result)
        self.assertIn("Bypass", result, "执行失败:" + text)

        text = "--step3:解析插入语句，由generate_series生成序列; " \
               "expect:解析结果包含：【No Bypass】"
        self.log.info(text)
        sql4 = f'''explain insert into {self.t_name1} select id, 
                random()::TINYINT, random()::BINARY_INTEGER, -25, 
                True, empty_blob(), 'DEADBEEF', E'\\xDEADBEEF', 
                '192.168/25', B'101', '{{{{\\"f1\\":1,
                \\"f2\\":true,\\"f3\\":\\"Hi\\"}}}}', 564182 
                from (select * from generate_series(15,20000) AS id) AS x;'''
        self.log.info(sql4)
        result = self.commonsh.execut_db_sql(sql4)
        self.log.info(result)
        self.assertIn("No Bypass", result, "执行失败:" + text)

        text = "--step4:解析插入语句，有多个values; expect:解析结果包含：【No Bypass】"
        self.log.info(text)
        sql5 = f'''explain insert into {self.t_name2} 
                values('wqw', '+;^kkk', '$￥你好', '哇哦@~ 777', 
                '2012-12-2', '21:21:21 pst', '2003-04-12 04:05:06', 
                point(1,2), '08:00:2b:01:02:03', B'10', 
                'The Fat Rats'::tsvector, 
                '{{{{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}}}}'), 
                ('wqw', '+;^kkk', '$￥你好', '哇哦@~ 777', '2012-12-20', 
                '21:21:21 pst', '2003-04-12 04:05:06', point(1,2), 
                '08:00:2b:01:02:03', B'10', 'The Fat Rats'::tsvector, 
                '{{{{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}}}}')'''
        self.log.info(sql5)
        result = self.commonsh.execut_db_sql(sql5)
        self.log.info(result)
        self.assertIn("No Bypass", result, "执行失败:" + text)

        text = "--step5:插入数据; expect:成功"
        self.log.info(text)
        sql6 = f'''insert into {self.t_name1} 
                select id, random()::TINYINT, random()::BINARY_INTEGER, 
                -25, true, empty_blob(), 'DEADBEEF', E'\\xDEADBEEF', 
                '192.168/25', B'101', '{{{{\\"f1\\":1,\\"f2\\":true,
                \\"f3\\":\\"Hi\\"}}}}', 564182 
                from (select * from generate_series(15,20000) AS id) AS x;
                insert into {self.t_name1} 
                values(10, 255, -3, +123, null, empty_blob(), 'DEADBEEF', 
                E'\\xDEADBEEF', '192.168/25', B'101', 
                '{{{{\\"f1\\":1,\\"f2\\":true,\\"f3\\":\\"Hi\\"}}}}', 564182);
                insert into {self.t_name2} 
                values('qw', '+;^kkk', '$￥你好', '哇哦@~ 777', 
                '2012-12-20', '21:21:21 pst', '2003-04-12 04:05:06', 
                point(1,2), '08:00:2b:01:02:03', B'10', 
                'The Fat Rats'::tsvector, 
                '{{{{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}}}}');'''
        self.log.info(sql6)
        result = self.commonsh.execut_db_sql(sql6)
        self.log.info(result)
        self.assertIn("INSERT", result, "执行失败:" + text)

        text = "--step6:创建索引; expect:成功"
        self.log.info(text)
        sql7 = f'''create index id_int_idx on {self.t_name1}(id_int);
                create index {self.t_name2}_idx 
                on {self.t_name2}(body1) where body1 = 'qw';'''
        self.log.info(sql7)
        result = self.commonsh.execut_db_sql(sql7)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, result,
                      "执行失败:" + text)

        text = "--step7:解析简单查询语句; expect:解析结果包含【Bypass】"
        self.log.info(text)
        sql8 = f'''explain select * from {self.t_name1} where id_int =10;
                explain select * from {self.t_name2} 
                where body1 = \'qw\''''
        sql_list = sql8.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn("Bypass", result, "执行失败:" + text)

        text = "--step8:查看查询结果; expect:成功"
        self.log.info(text)
        sql9 = f"select * from {self.t_name2} where body1 = 'qw'"
        result = self.commonsh.execut_db_sql(sql9)
        self.log.info(result)
        info_list = ["+;^kkk", "$￥你好", "哇哦@~ 777", "2012-12-20",
                     "21:21:21-08", "2003-04-12 04:05:00", "(1,2)",
                     "08:00:2b:01:02:03", "'Fat' 'Rats' 'The'",
                     "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"]
        for info in info_list:
            self.assertIn(info, result, "执行失败:" + text)

        sql = f"select * from {self.t_name1} where id_int =15"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        info_list = ["-$25.00", "t", "192.168.0.0/25", "101", "564182"]
        for info in info_list:
            self.assertIn(info, result, "执行失败:" + text)

    def tearDown(self):
        text = "--step9:清理环境; expect:成功"
        self.log.info(text)
        self.set_gs_guc("enable_opfusion", f"{self.enable_opfusion}", "reload")
        self.set_gs_guc("enable_bitmapscan", f"{self.enable_bitmapscan}",
                        "reload")
        self.set_gs_guc("enable_seqscan", f"{self.enable_seqscan}", "reload")
        self.set_gs_guc("opfusion_debug_mode", f"{self.opfusion_debug_mode}",
                        "reload")
        sql = f"drop table {self.t_name1} cascade;" \
            f"drop table {self.t_name2} cascade"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result, "执行失败:" + text)
        self.assertTrue("Normal" in status or "Degraded" in status)
        self.log.info(f"-----{os.path.basename(__file__)} finish=")
